﻿--获取订单(分配维护客服时用)
CREATE PROCEDURE [dbo].[proc_BDOrder_Getlist_AllotService]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@cusflag int --1按客服名查，0按客户名查
	)
AS
begin
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

	Declare @cSql varchar(2000)
	Set @cSql = 'CompanyId ='+Convert(varchar,@CompanyId)+' And NOE_Flag=0 And [Order].CreateDate>=@sRq And [Order].CreateDate<=@eRq'

	If @StateId>0
		Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)
	else
		Set @cSql = @cSql + ' And StateId in (3,4,5) '
		
	If @TypeId<>0
		Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

	If @WayId<>0
		Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

	If @ProcessId<>0
		Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

	If @Stext<>''
		if @cusflag=0
			Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' )'
		else
			Set @cSql = @cSql + ' And (trackBDName like ''%'+@Stext+'%'')'

	if @DepId<>0
		Set @cSql = @cSql +' and DepId in(Select Id From department Where Left(bmbh,Len('''+@bmbh_T+'''))='''+@bmbh_T+''')'

set @cSql='

	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,'''+@StartDate+' 00:00:00'')
	Set @eRq = Convert(Datetime,'''+@EndDate+' 23:59:59'')

;WITH list As(Select	Top 2000
		[Order].Id
		,OId
		,isnull(OrderId,0) as OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,[Order].TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,[Order].CreateDate
		,IsNull(YingShouPrice,0) as YingShouPrice
		,IsNull(ShiShouPrice,0) as ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		--,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,[Order].CompanyId
		,ExCompanyId
		,[Order].StateId
		,IsGreen
		,TrackKfName
		,TrackBDAccount As TrackKfAccount
		,IsNull(TrackKfDepId,0) As TrackKfDepId
		,IsNull(FinanceRenlingDate,''1900-01-01'') As FinanceRenlingDate
		,IsNull( trackBDName,'''') as BDServiceName
		From [Order]
		Where  '+@cSql+'
	),
	list2 as (
		select distinct orderid from Project where Project.companyid='+Convert(varchar,@CompanyId)+' 
	 and Project.productid =1 and Project.CreateDate>=@sRq
	),

	list1 as (
		select ROW_NUMBER() OVER (ORDER BY BDServiceName,CreateDate DESC,Id DESC)AS Row,* from 
		(select  distinct list.* from list left JOIN list2 on list.oid =list2.orderid 
		) as ab
	)

	Select *,(Select Count(0) From list1) As RecordCount,
	IsNull((Select Top 1 [State] From Employee Where UserName=TrackKfAccount),0) As BankId,	--人员在职状态
	IsNull((Select Title From Order_State Where Id=list1.StateId),''...'') As StateName
	From list1
	Where Row Between '+Convert(varchar,@StartIndex)+' And '+Convert(varchar,@EndIndex)+' Order By Row
'

	exec (@cSql)
end